IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetArraNumberOfJobs]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GetArraNumberOfJobs]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

/******************************************************************
*  Name:  GetArraNumberOfJobs
* Purpose:  
*
* PARAMETERS
* Name    			Description					
* -------------  	-------------------------------------------
* @PIQuesId
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------- 
* @NoJob		
******************************************************************/
CREATE FUNCTION [dbo].[GetArraNumberOfJobs] (
	@PIQuesId	INT
	
)
RETURNS DECIMAL(10,2)

AS 
BEGIN

	DECLARE @JobNo DECIMAL(10,2)

	SELECT @JobNo = SUM(apjob.PercentEffort)/100 FROM ArraPIQuestionnaire apiq 
	INNER JOIN ArraPIQuestionnaireJob apjob ON apiq.Id = apjob.ArraPIQuestionnaireID 
	AND (apjob.ArraPIQuestionnaireJobTypeID = 1 OR apjob.ArraPIQuestionnaireJobTypeID = 2)
	WHERE
	apiq.Id = @PIQuesId

	RETURN @JobNo
END


